﻿create PROCEDURE Sync.SP_wfProcessInstances_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @wfProcessInstances_Temp TABLE 
		(
			ProcessInstanceID UniqueIdentifier,
			[ProcessID] uniqueidentifier,
				[UserID] uniqueidentifier,
				[CreationDate] datetime,
				[CompletionDate] datetime,
				[EntityID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @wfProcessInstances_Temp 
	   SELECT	Tbl.Col.value('ProcessInstanceID[1]','uniqueidentifier') ProcessInstanceID,
                Tbl.Col.value('ProcessID[1]', 'uniqueidentifier') [ProcessID],
Tbl.Col.value('UserID[1]', 'uniqueidentifier') [UserID],
Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('CompletionDate[1]', 'datetime') [CompletionDate],
Tbl.Col.value('EntityID[1]', 'uniqueidentifier') [EntityID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/wfProcessInstances') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[wfProcessInstances] ([ProcessInstanceID],[ProcessID],[UserID],[CreationDate],[CompletionDate],[EntityID])
	SELECT t.[ProcessInstanceID],t.[ProcessID],t.[UserID],t.[CreationDate],t.[CompletionDate],t.[EntityID]
	FROM  @wfProcessInstances_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.wfProcessInstances_Tracking s
        WHERE t.ProcessInstanceID = s.ProcessInstanceID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[wfProcessInstances]
	SET [wfProcessInstances].[ProcessID] = t.[ProcessID],[wfProcessInstances].[UserID] = t.[UserID],[wfProcessInstances].[CreationDate] = t.[CreationDate],[wfProcessInstances].[CompletionDate] = t.[CompletionDate],[wfProcessInstances].[EntityID] = t.[EntityID] 
	FROM [dbo].[wfProcessInstances] s JOIN @wfProcessInstances_Temp t 
    ON t.ProcessInstanceID = s.ProcessInstanceID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.wfProcessInstances_Tracking r
				  WHERE t.ProcessInstanceID = r.ProcessInstanceID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[wfProcessInstances]
    FROM @wfProcessInstances_Temp t JOIN [dbo].[wfProcessInstances] s
    ON t.ProcessInstanceID = s.ProcessInstanceID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











